-- 
-- DEFINICE TABULEK
-- 


CREATE TABLE sources (
	id					SMALLINT PRIMARY KEY,
	key					TEXT,
	last_erase			TIMESTAMP DEFAULT now()
);

CREATE TABLE events (
	id				BIGSERIAL PRIMARY KEY,
	--
	date			TIMESTAMP NOT NULL DEFAULT now(),
	source			SMALLINT references sources(id)
						ON DELETE CASCADE ON UPDATE CASCADE,
	message			TEXT NOT NULL,
	xml				TEXT,
	count			INT DEFAULT 1 CHECK (count >= 0)
);

CREATE TABLE incidents (
	id				BIGSERIAL PRIMARY KEY,
	--
	domain			TEXT NOT NULL,
	domain_key		TEXT NOT NULL,
	count			INT DEFAULT 0 CHECK (count >= 0)
);

CREATE TABLE events_incidents (
	id_event		INT8 references events(id)
						ON DELETE CASCADE ON UPDATE CASCADE,
	id_incident		INT8 references incidents(id) 
						ON DELETE CASCADE ON UPDATE CASCADE
); 

CREATE TABLE services (
	key					TEXT PRIMARY KEY,
	id_event			BIGINT DEFAULT NULL
);

CREATE TABLE file_desc (
	id				SMALLINT references sources(id)
						ON DELETE CASCADE ON UPDATE CASCADE,
	seek			BIGINT,
	inode			BIGINT
);

CREATE TABLE reports (
	id				SERIAL PRIMARY KEY,
	src				TEXT NOT NULL,
	dst				TEXT NOT NULL,
	login			TEXT NOT NULL,
	count				SMALLINT DEFAULT 1 CHECK (count >= 0)
);

CREATE TABLE report_dates (
	id				INT references reports(id)
						ON DELETE CASCADE ON UPDATE CASCADE,
	date			TIMESTAMP DEFAULT now()
);

---FUNKCE V DB---------------------------------------------------------

-- Vlozeni udalosti
CREATE OR REPLACE FUNCTION insert_event(curr_date TIMESTAMP,source_id INT, 
message TEXT,xml TEXT)	RETURNS BIGINT AS $$

BEGIN
	INSERT INTO events VALUES(
		DEFAULT,curr_date,source_id, message,xml,DEFAULT);
	RETURN (SELECT currval('events_id_seq'));
END;
$$ LANGUAGE plpgsql;

--Pretizeni funkce vlozeni udalosti
CREATE OR REPLACE FUNCTION insert_event(source_id INT, message TEXT,xml TEXT) 
	RETURNS BIGINT AS $$
BEGIN
	INSERT INTO events VALUES(DEFAULT,DEFAULT, source_id, message,xml,DEFAULT);
	RETURN (SELECT currval('events_id_seq'));
END;
$$ LANGUAGE plpgsql;


------
-- Vlozeni incidentu
CREATE OR REPLACE FUNCTION insert_incident 
	(id_event BIGINT,dom TEXT, dom_key TEXT) RETURNS BIGINT AS $$

DECLARE
	new_record BOOL;
	id_incident BIGINT;

BEGIN
	new_record = EXISTS (
		SELECT 1 FROM incidents WHERE domain = dom AND domain_key = dom_key
	);
	IF new_record = 'f' THEN
		INSERT INTO incidents VALUES(DEFAULT,dom,dom_key,DEFAULT);
	END IF;

--Pokud je incident novy, zjistuje se currvalem, jinak pomoci domeny a klice
	IF new_record = 'f' THEN
		id_incident = (SELECT currval('incidents_id_seq'));
	ELSE
		id_incident = (
			SELECT id FROM incidents 
				WHERE domain = dom AND domain_key = dom_key
		);
	END IF;

	INSERT INTO events_incidents VALUES (id_event,id_incident);
	RETURN id_incident;
END;
$$ LANGUAGE plpgsql;

--- Vlozeni nebo aktualizace deskriptoru
CREATE OR REPLACE FUNCTION insert_fd
	(fd_id SMALLINT, fd_seek BIGINT, fd_inode BIGINT) RETURNS VOID AS $$

DECLARE
	new_record BOOL;
BEGIN
	new_record = EXISTS
		(SELECT 1 FROM file_desc WHERE id = fd_id);
	IF new_record = 't' THEN
		UPDATE file_desc SET seek = fd_seek, inode = fd_inode WHERE id = fd_id;
	ELSE
		INSERT INTO file_desc VALUES (fd_id,fd_seek,fd_inode);
	END IF;
	RETURN;
END;
$$ LANGUAGE plpgsql;

------
-- Smazani udalosti na zaklade ID
CREATE OR REPLACE FUNCTION delete_event
(id_event bigint) RETURNS VOID AS $$

BEGIN
	DELETE FROM events WHERE id = id_event;
	RETURN;
END;
$$ LANGUAGE plpgsql;


-----------
-- Pretizene funkce vymazu incidentu pomoci ID nebo domeny a klice
CREATE OR REPLACE FUNCTION delete_incident
(id_to_erase BIGINT,id_change BIGINT[]) RETURNS VOID AS $$

BEGIN
	DELETE FROM events_incidents WHERE id_event IN (
		SELECT id_event FROM events_incidents 
			WHERE id_incident = id_to_erase INTERSECT 
		SELECT id_event FROM events_incidents 
			WHERE id_incident = ANY (id_change) 
		)
		AND (id_incident = ANY(id_change) OR id_incident = id_to_erase);
	RETURN;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION delete_incident
(id_to_erase BIGINT) RETURNS VOID AS $$

BEGIN
	DELETE FROM incidents WHERE id = id_to_erase;
	RETURN;
END;
$$ LANGUAGE plpgsql;

----------
-- Funkce aktualizuje count v incidentu na zaklade jeho ID
-- pokud je count 0, incident se smaze
CREATE OR REPLACE FUNCTION update_incident_count
	( incident_id BIGINT ) RETURNS VOID AS $$

DECLARE
	sum_count BIGINT;
BEGIN
	SELECT SUM(count) INTO sum_count FROM events ,events_incidents
		WHERE events.id = events_incidents.id_event
			AND events_incidents.id_incident = incident_id;	

IF sum_count IS NULL THEN
	DELETE FROM incidents WHERE id = incident_id;
	RETURN;
END IF;

UPDATE incidents
	SET count = sum_count
	WHERE id = incident_id;

	RETURN;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION update_event
	(id_event BIGINT, curr_date TIMESTAMP, curr_count INT) RETURNS VOID AS $$
BEGIN
	UPDATE events SET date = curr_date, count = curr_count WHERE id = id_event;
	RETURN;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION update_event
	(id_event BIGINT, curr_count INT) RETURNS VOID AS $$
BEGIN
	UPDATE events SET date = now(), count = curr_count WHERE id = id_event;
	RETURN;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION time_triggers() RETURNS VOID AS $$

BEGIN
	SELECT id INTO ids FROM events WHERE (
 		EXTRACT(epoch FROM now() - date)/3600 > 1 
	);
	RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION insert_service 
( key_service TEXT, curr_id_event BIGINT ) RETURNS VOID AS $$
DECLARE exist BOOL;
BEGIN
	exist = EXISTS 
		(SELECT 1 FROM services WHERE
 			key = key_service AND id_event = curr_id_event
		);
	
	IF exist = 'f' THEN
		DELETE FROM services WHERE key = key_service;
		INSERT INTO services VALUES (key_service,curr_id_event);
	END IF;

	RETURN;	
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION insert_service 
( key_service TEXT) RETURNS VOID AS $$
DECLARE exist BOOL;
BEGIN
	exist = EXISTS (
		SELECT 1 FROM services WHERE key = key_service AND id_event IS NULL
	);
	
	IF exist = 'f' THEN
		DELETE FROM services WHERE key = key_service;
		INSERT INTO services VALUES (key_service,DEFAULT);
	END IF;

	RETURN;
END;
$$ LANGUAGE plpgsql;

-----------------------

CREATE OR REPLACE FUNCTION erase_old_events (source_id INT, duration INTERVAL) 
RETURNS VOID AS $$
BEGIN
	DELETE FROM events	WHERE (
		SELECT (events.date, events.date) OVERLAPS (
 			(now() - duration ), now() 
		) 
	) IS FALSE AND (events.source = source_id);
	RETURN;
END;	
$$ LANGUAGE plpgsql;

----------------

CREATE OR REPLACE FUNCTION insert_report(
	curr_src TEXT, curr_dst TEXT, curr_login TEXT
) RETURNS VOID AS $$
DECLARE
	exist BOOL ;
	report_id INT;
BEGIN
	exist = EXISTS (SELECT 1 FROM reports WHERE
				src = curr_src AND dst = curr_dst AND login = curr_login
			);
	IF exist = 't' THEN
		UPDATE reports SET count = count+1
			WHERE src = curr_src AND dst = curr_dst AND login = curr_login;
		report_id = (SELECT id FROM reports WHERE
					src = curr_src AND dst = curr_dst AND login = curr_login
		);
		INSERT INTO report_dates VALUES (report_id, now() );
	ELSE
		INSERT INTO reports VALUES (
			DEFAULT, curr_src, curr_dst, curr_login, '1'
		);
		INSERT INTO report_dates VALUES( currval('reports_id_seq'), now());
	END IF;
	RETURN;
END;
$$ LANGUAGE plpgsql;

--------------------------

CREATE OR REPLACE FUNCTION erase_old_reports (duration INTERVAL
 ) RETURNS VOID AS $$
BEGIN
	DELETE FROM reports WHERE (
			(SELECT MAX(date) FROM report_dates WHERE
 				report_dates.id = reports.id) - (now() - duration) ) < 0;
	RETURN;
END;
$$ LANGUAGE plpgsql;

-----TRIGGERY A JEJICH FUNKCE--------------------------------------------------

CREATE OR REPLACE FUNCTION update_incident_by_event() RETURNS trigger AS $$
BEGIN

IF (OLD.count <> NEW.count) THEN
	UPDATE incidents 
	SET count = (
		SELECT SUM(count) FROM events ,events_incidents 
			WHERE events.id = events_incidents.id_event 
				AND events_incidents.id_incident = incidents.id
		)
	WHERE id IN (
		SELECT events_incidents.id_incident FROM events_incidents 
			WHERE events_incidents.id_event = NEW.id
	);
END IF;

RETURN NULL;
END
$$ LANGUAGE plpgsql;


--Trigger aktualizuje pocty v incidentech pokud se zmenil udaj v udalostech
--ktere jsou spjaty s danymi incidenty

CREATE TRIGGER update_event_trigger AFTER UPDATE ON events FOR EACH ROW EXECUTE
PROCEDURE update_incident_by_event();

--------------------------
CREATE OR REPLACE FUNCTION update_incident_by_delete_e_i() 
	RETURNS trigger AS $$

DECLARE
	v_count		INT;
	skip_inc		BOOL;
	skip_event		BOOL;	

BEGIN
	skip_event = EXISTS (SELECT 1 FROM events WHERE id = OLD.id_event);
	EXECUTE update_incident_count(OLD.id_incident);

RETURN NULL;
END
$$ LANGUAGE plpgsql;

--Trigger aktualizuje incidenty,jimz se smazali zaznamy z tab. events_incidents
CREATE TRIGGER delete_e_i_after AFTER DELETE ON events_incidents FOR EACH ROW 
EXECUTE PROCEDURE update_incident_by_delete_e_i();

---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION update_incident_by_e_i() RETURNS trigger AS $$
BEGIN
	EXECUTE update_incident_count(NEW.id_incident);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_incident_after_e_i AFTER INSERT ON events_incidents 
FOR EACH ROW EXECUTE PROCEDURE update_incident_by_e_i();
---------------------------------------------------------------------